#![allow(unused_variables)]
use sqlx::Error;
use super::CountNum;
use super::RoleBean;
use super::Table;
use super::UserBean;
use super::UserQueryRequest;

impl<'c> Table<'c, RoleBean> {
pub async fn list_active_user_roles_using_user_id(&self,user_id:i64) ->Result<Vec<RoleBean>, Error> {
		let result = sqlx::query_as(
			r#"select distinct cs_roles.id,cs_roles.role_name,cs_roles.active from cs_roles,cs_user_role where cs_roles.id = cs_user_role.role_id and cs_user_role.user_id = ? and cs_roles.active = true order by cs_roles.role_name asc "#
		)
		.bind(user_id)
		.fetch_all(&(&*self.pool).clone().unwrap())
		.await;
		return result;
}

}
impl<'c> Table<'c, UserBean> {
pub async fn activate_user(&self,id:i64) ->Result<u64, Error> {
		let result = sqlx::query(
	r#"update cs_users set active = true where id = ? "#
		)
		.bind(id)
		.execute(&(&*self.pool).clone().unwrap())
		.await;
		match result {
			Ok(result) => {
				Ok(result.rows_affected())
			},
			Err(err) => {
				Err(err)
			}
		}
}

pub async fn add_user(&self,user:UserBean) ->Result<u64, Error> {
		let result = sqlx::query(
		r#"insert into cs_users ( user_name,active,first_name,last_name,email,isadmin,password,gender,salt,login_failure,address,phone,description,photo) values (?,?,?,?,?,?,?,?,?,?,?,?,?,from_base64(?))"#
		)
		.bind(user.user_name)
		.bind(user.active)
		.bind(user.first_name)
		.bind(user.last_name)
		.bind(user.email)
		.bind(user.isadmin)
		.bind(user.password)
		.bind(user.gender)
		.bind(user.salt)
		.bind(user.login_failure)
		.bind(user.address)
		.bind(user.phone)
		.bind(user.description)
		.bind(user.photo)
		.execute(&(&*self.pool).clone().unwrap())
		.await;
		match result {
			Ok(result) => {
				Ok(result.rows_affected())
			},
			Err(err) => {
				Err(err)
			}
		}
}

pub async fn assign_role_to_user(&self,user_id:i64,role_id:i64) ->Result<u64, Error> {
		let result = sqlx::query(
			r#"insert into cs_user_role ( user_id,role_id) values (?,?)"#
		)
		.bind(user_id)
		.bind(role_id)
		.execute(&(&*self.pool).clone().unwrap())
		.await;
		match result {
			Ok(result) => {
				Ok(result.rows_affected())
			},
			Err(err) => {
				Err(err)
			}
		}
}

pub async fn change_password_user(&self,user:UserBean) ->Result<u64, Error> {
	let result = sqlx::query(
		r#"update cs_users set active=true,password = ?,salt = ?,login_failure = 0 where user_name = ? "#
	)
	.bind(user.password)
	.bind(user.salt)
	.bind(user.user_name)
	.execute(&(&*self.pool).clone().unwrap())
	.await;
		match result {
			Ok(result) => {
				Ok(result.rows_affected())
			},
			Err(err) => {
				Err(err)
			}
		}
}

pub async fn count_active_user_records(&self) ->Result<CountNum, Error> {
		let result = sqlx::query_as(
		r#"select count(*) as countNum from cs_users where active = true"#
		)
		.fetch_one(&(&*self.pool).clone().unwrap())
		.await;
		return result;
}

pub async fn count_salt(&self,salt:String) ->Result<CountNum, Error> {
		let result = sqlx::query_as(
			r#"select count(id) as countNum from  cs_users where salt=?"#
		)
		.bind(salt)
		.fetch_one(&(&*self.pool).clone().unwrap())
		.await;
		return result;
}

pub async fn count_search_users_by_fields_records(&self,user_query_request:UserQueryRequest) ->Result<CountNum, Error> {
		let mut query = r#"select count(*) as countNum from cs_users where 1=1 "#.to_string();
		let user_name = user_query_request.user_name.unwrap_or_default();
		if !user_name.is_empty() {
			let query_user_name  = format!("{}{}{}"," and user_name like '%",user_name,"%' ");
			query = format!("{} {}", query, query_user_name);
		}
		let active = user_query_request.active.unwrap_or_default();
		if !active.is_empty() && (active=="true" || active =="false"){
			let query_active  = format!("{}{}{}"," and active = ",active," ");
			query = format!("{} {}", query, query_active);
		}
		let first_name = user_query_request.first_name.unwrap_or_default();
		if !first_name.is_empty() {
			let query_first_name  = format!("{}{}{}"," and first_name like '%",first_name,"%' ");
			query = format!("{} {}", query, query_first_name);
		}
		let last_name = user_query_request.last_name.unwrap_or_default();
		if !last_name.is_empty() {
			let query_last_name  = format!("{}{}{}"," and last_name like '%",last_name,"%' ");
			query = format!("{} {}", query, query_last_name);
		}
		let email = user_query_request.email.unwrap_or_default();
		if !email.is_empty() {
			let query_email  = format!("{}{}{}"," and email like '%",email,"%' ");
			query = format!("{} {}", query, query_email);
		}
		let isadmin = user_query_request.isadmin.unwrap_or_default();
		if !isadmin.is_empty() && (isadmin=="true" || isadmin =="false"){
			let query_isadmin  = format!("{}{}{}"," and isadmin = ",isadmin," ");
			query = format!("{} {}", query, query_isadmin);
		}
		let gender = user_query_request.gender.unwrap_or_default();
		if !gender.is_empty() {
			let query_gender  = format!("{}{}{}"," and gender like '%",gender,"%' ");
			query = format!("{} {}", query, query_gender);
		}
		let address = user_query_request.address.unwrap_or_default();
		if !address.is_empty() {
			let query_address  = format!("{}{}{}"," and address like '%",address,"%' ");
			query = format!("{} {}", query, query_address);
		}
		let phone = user_query_request.phone.unwrap_or_default();
		if !phone.is_empty() {
			let query_phone  = format!("{}{}{}"," and phone like '%",phone,"%' ");
			query = format!("{} {}", query, query_phone);
		}
		let description = user_query_request.description.unwrap_or_default();
		if !description.is_empty() {
			let query_description  = format!("{}{}{}"," and description like '%",description,"%' ");
			query = format!("{} {}", query, query_description);
		}
		let result = sqlx::query_as(&query)
		.fetch_one(&(&*self.pool).clone().unwrap())
		.await;
		return result;
}

pub async fn delete_user(&self,id:i64) ->Result<u64, Error> {
		let result = sqlx::query(
	r#"delete from cs_users where id = ? "#
		)
		.bind(id)
		.execute(&(&*self.pool).clone().unwrap())
		.await;
		match result {
			Ok(result) => {
				Ok(result.rows_affected())
			},
			Err(err) => {
				Err(err)
			}
		}
}

pub async fn find_user_by_id(&self,id:i64) ->Result<UserBean, Error> {
	let result = sqlx::query_as(
	r#"select id,user_name,active,first_name,last_name,email,isadmin, '' as password,gender, '' as salt, 0 as login_failure,address,phone,description,photo from cs_users where id = ? "#
		)
		.bind(id)
		.fetch_one(&(&*self.pool).clone().unwrap())
		.await;
		return result;
}

pub async fn find_user_by_user_name(&self,user_name:String) ->Result<UserBean, Error> {
	let result = sqlx::query_as(
	r#"select id,user_name,active,first_name,last_name,email,isadmin, '' as password,gender, '' as salt, 0 as login_failure,address,phone,description,photo from cs_users where user_name = ?;"#
		)
		.bind(user_name)
		.fetch_one(&(&*self.pool).clone().unwrap())
		.await;
		return result;
}

pub async fn find_user_shadow_by_user_name(&self,user_name:String) ->Result<UserBean, Error> {
	let result = sqlx::query_as(
	r#"select id,user_name,active,first_name,last_name,email,isadmin,password,gender,salt,login_failure,address,phone,description,photo from cs_users where user_name = ? "#
		)
		.bind(user_name)
		.fetch_one(&(&*self.pool).clone().unwrap())
		.await;
		return result;
}

pub async fn list_active_users(&self) ->Result<Vec<UserBean>, Error> {
	let result = sqlx::query_as(
	   r#"select id,user_name,active,first_name,last_name,email,isadmin, '' as password,gender, '' as salt, 0 as login_failure,address,phone,description,photo from cs_users where active = true order by id asc"#
	)
	.fetch_all(&(&*self.pool).clone().unwrap())
	.await;
	return result;
}

pub async fn list_all_users(&self) ->Result<Vec<UserBean>, Error> {
	let result = sqlx::query_as(
	  r#"select id,user_name,active,first_name,last_name,email,isadmin, '' as password,gender, '' as salt, 0 as login_failure,address,phone,description,photo from cs_users"#
	)
	.fetch_all(&(&*self.pool).clone().unwrap())
	.await;
	return result;
}

pub async fn register_user(&self,user:UserBean) ->Result<u64, Error> {
		let result = sqlx::query(
		r#"insert into cs_users ( user_name,active,first_name,last_name,email,isadmin,password,gender,salt,login_failure,address,phone,description,photo) values (?,?,?,?,?,?,?,?,?,?,?,?,?,from_base64(?))"#
		)
		.bind(user.user_name)
		.bind(user.active)
		.bind(user.first_name)
		.bind(user.last_name)
		.bind(user.email)
		.bind(user.isadmin)
		.bind(user.password)
		.bind(user.gender)
		.bind(user.salt)
		.bind(user.login_failure)
		.bind(user.address)
		.bind(user.phone)
		.bind(user.description)
		.bind(user.photo)
		.execute(&(&*self.pool).clone().unwrap())
		.await;
		match result {
			Ok(result) => {
				Ok(result.rows_affected())
			},
			Err(err) => {
				Err(err)
			}
		}
}

pub async fn revoke_role_from_user(&self,user_id:i64,role_id:i64) ->Result<u64, Error> {
		let result = sqlx::query(
			r#"delete from cs_user_role  where user_id = ? and role_id = ? "#
		)
		.bind(user_id)
		.bind(role_id)
		.execute(&(&*self.pool).clone().unwrap())
		.await;
		match result {
			Ok(result) => {
				Ok(result.rows_affected())
			},
			Err(err) => {
				Err(err)
			}
		}
}

pub async fn search_users_by_fields(&self,user_query_request:UserQueryRequest) ->Result<Vec<UserBean>, Error> {
		let mut query = r#"select id,user_name,active,first_name,last_name,email,isadmin, '' as password,gender, '' as salt, 0 as login_failure,address,phone,description,photo from cs_users where 1=1 "#.to_string();
		let user_name = user_query_request.user_name.unwrap_or_default();
		if !user_name.is_empty() {
			let query_user_name  = format!("{}{}{}"," and user_name like '%",user_name,"%' ");
			query = format!("{} {}", query, query_user_name);
		}
		let active = user_query_request.active.unwrap_or_default();
		if !active.is_empty() && (active=="true" || active =="false"){
			let query_active  = format!("{}{}{}"," and active = ",active," ");
			query = format!("{} {}", query, query_active);
		}
		let first_name = user_query_request.first_name.unwrap_or_default();
		if !first_name.is_empty() {
			let query_first_name  = format!("{}{}{}"," and first_name like '%",first_name,"%' ");
			query = format!("{} {}", query, query_first_name);
		}
		let last_name = user_query_request.last_name.unwrap_or_default();
		if !last_name.is_empty() {
			let query_last_name  = format!("{}{}{}"," and last_name like '%",last_name,"%' ");
			query = format!("{} {}", query, query_last_name);
		}
		let email = user_query_request.email.unwrap_or_default();
		if !email.is_empty() {
			let query_email  = format!("{}{}{}"," and email like '%",email,"%' ");
			query = format!("{} {}", query, query_email);
		}
		let isadmin = user_query_request.isadmin.unwrap_or_default();
		if !isadmin.is_empty() && (isadmin=="true" || isadmin =="false"){
			let query_isadmin  = format!("{}{}{}"," and isadmin = ",isadmin," ");
			query = format!("{} {}", query, query_isadmin);
		}
		let gender = user_query_request.gender.unwrap_or_default();
		if !gender.is_empty() {
			let query_gender  = format!("{}{}{}"," and gender like '%",gender,"%' ");
			query = format!("{} {}", query, query_gender);
		}
		let address = user_query_request.address.unwrap_or_default();
		if !address.is_empty() {
			let query_address  = format!("{}{}{}"," and address like '%",address,"%' ");
			query = format!("{} {}", query, query_address);
		}
		let phone = user_query_request.phone.unwrap_or_default();
		if !phone.is_empty() {
			let query_phone  = format!("{}{}{}"," and phone like '%",phone,"%' ");
			query = format!("{} {}", query, query_phone);
		}
		let description = user_query_request.description.unwrap_or_default();
		if !description.is_empty() {
			let query_description  = format!("{}{}{}"," and description like '%",description,"%' ");
			query = format!("{} {}", query, query_description);
		}
		let result = sqlx::query_as(&query)
		.fetch_all(&(&*self.pool).clone().unwrap())
		.await;
		return result;
}

pub async fn search_users_by_fields_by_page(&self,user_query_request:UserQueryRequest) ->Result<Vec<UserBean>, Error> {
		let mut query = r#"select id,user_name,active,first_name,last_name,email,isadmin, '' as password,gender, '' as salt, 0 as login_failure,address,phone,description,photo from cs_users where 1=1 "#.to_string();
		let user_name = user_query_request.user_name.unwrap_or_default();
		if !user_name.is_empty() {
			let query_user_name  = format!("{}{}{}"," and user_name like '%",user_name,"%' ");
			query = format!("{} {}", query, query_user_name);
		}
		let active = user_query_request.active.unwrap_or_default();
		if !active.is_empty() && (active=="true" || active =="false"){
			let query_active  = format!("{}{}{}"," and active = ",active," ");
			query = format!("{} {}", query, query_active);
		}
		let first_name = user_query_request.first_name.unwrap_or_default();
		if !first_name.is_empty() {
			let query_first_name  = format!("{}{}{}"," and first_name like '%",first_name,"%' ");
			query = format!("{} {}", query, query_first_name);
		}
		let last_name = user_query_request.last_name.unwrap_or_default();
		if !last_name.is_empty() {
			let query_last_name  = format!("{}{}{}"," and last_name like '%",last_name,"%' ");
			query = format!("{} {}", query, query_last_name);
		}
		let email = user_query_request.email.unwrap_or_default();
		if !email.is_empty() {
			let query_email  = format!("{}{}{}"," and email like '%",email,"%' ");
			query = format!("{} {}", query, query_email);
		}
		let isadmin = user_query_request.isadmin.unwrap_or_default();
		if !isadmin.is_empty() && (isadmin=="true" || isadmin =="false"){
			let query_isadmin  = format!("{}{}{}"," and isadmin = ",isadmin," ");
			query = format!("{} {}", query, query_isadmin);
		}
		let gender = user_query_request.gender.unwrap_or_default();
		if !gender.is_empty() {
			let query_gender  = format!("{}{}{}"," and gender like '%",gender,"%' ");
			query = format!("{} {}", query, query_gender);
		}
		let address = user_query_request.address.unwrap_or_default();
		if !address.is_empty() {
			let query_address  = format!("{}{}{}"," and address like '%",address,"%' ");
			query = format!("{} {}", query, query_address);
		}
		let phone = user_query_request.phone.unwrap_or_default();
		if !phone.is_empty() {
			let query_phone  = format!("{}{}{}"," and phone like '%",phone,"%' ");
			query = format!("{} {}", query, query_phone);
		}
		let description = user_query_request.description.unwrap_or_default();
		if !description.is_empty() {
			let query_description  = format!("{}{}{}"," and description like '%",description,"%' ");
			query = format!("{} {}", query, query_description);
		}
		let pagenum =  user_query_request.page.unwrap_or_default();
		let pagesize =  user_query_request.rows.unwrap_or_default();
		let start = (pagenum - 1) * pagesize;
		let limit = pagesize;
		query = format!("{} limit {} offset {} ", query, limit, start);
		let result = sqlx::query_as(&query)
		.fetch_all(&(&*self.pool).clone().unwrap())
		.await;
		return result;
}

pub async fn soft_delete_user(&self,id:i64) ->Result<u64, Error> {
		let result = sqlx::query(
		r#"update cs_users set active = false where id = ? "#
		)
		.bind(id)
		.execute(&(&*self.pool).clone().unwrap())
		.await;
		match result {
			Ok(result) => {
				Ok(result.rows_affected())
			},
			Err(err) => {
				Err(err)
			}
		}
}

pub async fn toggle_user(&self,id:i64) ->Result<u64, Error> {
		let result = sqlx::query(
	r#"update cs_users set active = not active where id = ? "#
		)
		.bind(id)
		.execute(&(&*self.pool).clone().unwrap())
		.await;
		match result {
			Ok(result) => {
				Ok(result.rows_affected())
			},
			Err(err) => {
				Err(err)
			}
		}
}

pub async fn update_user(&self,user:UserBean) ->Result<u64, Error> {
		let result = sqlx::query(
		r#"update cs_users set user_name = ? ,active = ? ,first_name = ? ,last_name = ? ,email = ? ,isadmin = ? ,password = ? ,gender = ? ,salt = ? ,login_failure = ? ,address = ? ,phone = ? ,description = ? ,photo = from_base64(?) where id = ? "#
		)
		.bind(user.user_name)
		.bind(user.active)
		.bind(user.first_name)
		.bind(user.last_name)
		.bind(user.email)
		.bind(user.isadmin)
		.bind(user.password)
		.bind(user.gender)
		.bind(user.salt)
		.bind(user.login_failure)
		.bind(user.address)
		.bind(user.phone)
		.bind(user.description)
		.bind(user.photo)
		.bind(user.id)
		.execute(&(&*self.pool).clone().unwrap())
		.await;
		match result {
			Ok(result) => {
				Ok(result.rows_affected())
			},
			Err(err) => {
				Err(err)
			}
		}
}

pub async fn find_user_indexed_name(&self,short_name:String) ->Result<Vec<UserBean>, Error> {
	let result = sqlx::query_as(
	r#"select id,user_name,active,first_name,last_name,email,isadmin,password,gender,salt,login_failure,address,phone,description,photo from cs_users where user_name like concat(? ,'%') "#
	)
	.bind(short_name)
	.fetch_all(&(&*self.pool).clone().unwrap())
	.await;
		return result;
}

}
